OBJECT Dataport 50098 DMS Universal Dataport
{
  OBJECT-PROPERTIES
  {
    Date=11/11/10;
    Time=[ 1:32:08 PM];
    Version List=DMS,UDP;
  }
  PROPERTIES
  {
    CaptionML=ENU=DMS Universal Dataport - dynms.com;
    FieldSeparator=<TAB>;
    OnPreDataport=BEGIN
                    CurrDataport.FILENAME(txtFileName);
                  END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000026;
        DataItemVarName=diInteger;
        AutoSave=No;
        AutoUpdate=No;
        DataItemTableView=SORTING(Number)
                          WHERE(Number=FILTER(>=0));
        OnPreDataItem=BEGIN
                        rrRecRef.OPEN(nTableID);
                      END;

        OnBeforeImportRecord=BEGIN
                               CLEAR(atxtFieldData);
                               rrRecRef.INIT;
                             END;

        OnAfterImportRecord=BEGIN
                              doAfterImportRecord;
                              diInteger.NEXT; // updates progress to show line # being imported
                            END;

      }
      FIELDS
      {
        {      ;     ;atxtFieldData[1]     }
        {      ;     ;atxtFieldData[2]     }
        {      ;     ;atxtFieldData[3]     }
        {      ;     ;atxtFieldData[4]     }
        {      ;     ;atxtFieldData[5]     }
        {      ;     ;atxtFieldData[6]     }
        {      ;     ;atxtFieldData[7]     }
        {      ;     ;atxtFieldData[8]     }
        {      ;     ;atxtFieldData[9]     }
        {      ;     ;atxtFieldData[10]    }
        {      ;     ;atxtFieldData[11]    }
        {      ;     ;atxtFieldData[12]    }
        {      ;     ;atxtFieldData[13]    }
        {      ;     ;atxtFieldData[14]    }
        {      ;     ;atxtFieldData[15]    }
        {      ;     ;atxtFieldData[16]    }
        {      ;     ;atxtFieldData[17]    }
        {      ;     ;atxtFieldData[18]    }
        {      ;     ;atxtFieldData[19]    }
        {      ;     ;atxtFieldData[20]    }
        {      ;     ;atxtFieldData[21]    }
        {      ;     ;atxtFieldData[22]    }
        {      ;     ;atxtFieldData[23]    }
        {      ;     ;atxtFieldData[24]    }
        {      ;     ;atxtFieldData[25]    }
        {      ;     ;atxtFieldData[26]    }
        {      ;     ;atxtFieldData[27]    }
        {      ;     ;atxtFieldData[28]    }
        {      ;     ;atxtFieldData[29]    }
        {      ;     ;atxtFieldData[30]    }
        {      ;     ;atxtFieldData[31]    }
        {      ;     ;atxtFieldData[32]    }
        {      ;     ;atxtFieldData[33]    }
        {      ;     ;atxtFieldData[34]    }
        {      ;     ;atxtFieldData[35]    }
        {      ;     ;atxtFieldData[36]    }
        {      ;     ;atxtFieldData[37]    }
        {      ;     ;atxtFieldData[38]    }
        {      ;     ;atxtFieldData[39]    }
        {      ;     ;atxtFieldData[40]    }
        {      ;     ;atxtFieldData[41]    }
        {      ;     ;atxtFieldData[42]    }
        {      ;     ;atxtFieldData[43]    }
        {      ;     ;atxtFieldData[44]    }
        {      ;     ;atxtFieldData[45]    }
        {      ;     ;atxtFieldData[46]    }
        {      ;     ;atxtFieldData[47]    }
        {      ;     ;atxtFieldData[48]    }
        {      ;     ;atxtFieldData[49]    }
        {      ;     ;atxtFieldData[50]    }
        {      ;     ;atxtFieldData[51]    }
        {      ;     ;atxtFieldData[52]    }
        {      ;     ;atxtFieldData[53]    }
        {      ;     ;atxtFieldData[54]    }
        {      ;     ;atxtFieldData[55]    }
        {      ;     ;atxtFieldData[56]    }
        {      ;     ;atxtFieldData[57]    }
        {      ;     ;atxtFieldData[58]    }
        {      ;     ;atxtFieldData[59]    }
        {      ;     ;atxtFieldData[60]    }
        {      ;     ;atxtFieldData[61]    }
        {      ;     ;atxtFieldData[62]    }
        {      ;     ;atxtFieldData[63]    }
        {      ;     ;atxtFieldData[64]    }
        {      ;     ;atxtFieldData[65]    }
        {      ;     ;atxtFieldData[66]    }
        {      ;     ;atxtFieldData[67]    }
        {      ;     ;atxtFieldData[68]    }
        {      ;     ;atxtFieldData[69]    }
        {      ;     ;atxtFieldData[70]    }
        {      ;     ;atxtFieldData[71]    }
        {      ;     ;atxtFieldData[72]    }
        {      ;     ;atxtFieldData[73]    }
        {      ;     ;atxtFieldData[74]    }
        {      ;     ;atxtFieldData[75]    }
        {      ;     ;atxtFieldData[76]    }
        {      ;     ;atxtFieldData[77]    }
        {      ;     ;atxtFieldData[78]    }
        {      ;     ;atxtFieldData[79]    }
        {      ;     ;atxtFieldData[80]    }
        {      ;     ;atxtFieldData[81]    }
        {      ;     ;atxtFieldData[82]    }
        {      ;     ;atxtFieldData[83]    }
        {      ;     ;atxtFieldData[84]    }
        {      ;     ;atxtFieldData[85]    }
        {      ;     ;atxtFieldData[86]    }
        {      ;     ;atxtFieldData[87]    }
        {      ;     ;atxtFieldData[88]    }
        {      ;     ;atxtFieldData[89]    }
        {      ;     ;atxtFieldData[90]    }
        {      ;     ;atxtFieldData[91]    }
        {      ;     ;atxtFieldData[92]    }
        {      ;     ;atxtFieldData[93]    }
        {      ;     ;atxtFieldData[94]    }
        {      ;     ;atxtFieldData[95]    }
        {      ;     ;atxtFieldData[96]    }
        {      ;     ;atxtFieldData[97]    }
        {      ;     ;atxtFieldData[98]    }
        {      ;     ;atxtFieldData[99]    }
        {      ;     ;atxtFieldData[100]   }
        {      ;     ;atxtFieldData[101]   }
        {      ;     ;atxtFieldData[102]   }
        {      ;     ;atxtFieldData[103]   }
        {      ;     ;atxtFieldData[104]   }
        {      ;     ;atxtFieldData[105]   }
        {      ;     ;atxtFieldData[106]   }
        {      ;     ;atxtFieldData[107]   }
        {      ;     ;atxtFieldData[108]   }
        {      ;     ;atxtFieldData[109]   }
        {      ;     ;atxtFieldData[110]   }
        {      ;     ;atxtFieldData[111]   }
        {      ;     ;atxtFieldData[112]   }
        {      ;     ;atxtFieldData[113]   }
        {      ;     ;atxtFieldData[114]   }
        {      ;     ;atxtFieldData[115]   }
        {      ;     ;atxtFieldData[116]   }
        {      ;     ;atxtFieldData[117]   }
        {      ;     ;atxtFieldData[118]   }
        {      ;     ;atxtFieldData[119]   }
        {      ;     ;atxtFieldData[120]   }
        {      ;     ;atxtFieldData[121]   }
        {      ;     ;atxtFieldData[122]   }
        {      ;     ;atxtFieldData[123]   }
        {      ;     ;atxtFieldData[124]   }
        {      ;     ;atxtFieldData[125]   }
        {      ;     ;atxtFieldData[126]   }
        {      ;     ;atxtFieldData[127]   }
        {      ;     ;atxtFieldData[128]   }
        {      ;     ;atxtFieldData[129]   }
        {      ;     ;atxtFieldData[130]   }
        {      ;     ;atxtFieldData[131]   }
        {      ;     ;atxtFieldData[132]   }
        {      ;     ;atxtFieldData[133]   }
        {      ;     ;atxtFieldData[134]   }
        {      ;     ;atxtFieldData[135]   }
        {      ;     ;atxtFieldData[136]   }
        {      ;     ;atxtFieldData[137]   }
        {      ;     ;atxtFieldData[138]   }
        {      ;     ;atxtFieldData[139]   }
        {      ;     ;atxtFieldData[140]   }
        {      ;     ;atxtFieldData[141]   }
        {      ;     ;atxtFieldData[142]   }
        {      ;     ;atxtFieldData[143]   }
        {      ;     ;atxtFieldData[144]   }
        {      ;     ;atxtFieldData[145]   }
        {      ;     ;atxtFieldData[146]   }
        {      ;     ;atxtFieldData[147]   }
        {      ;     ;atxtFieldData[148]   }
        {      ;     ;atxtFieldData[149]   }
        {      ;     ;atxtFieldData[150]   }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=10780;
      Height=4730;
      SaveValues=Yes;
      OnInit=BEGIN
               txtTableName := '';
             END;

    }
    CONTROLS
    {
      { 1   ;TextBox      ;4620 ;110  ;6050 ;440  ;InPage=-1;
                                                   AssistEdit=Yes;
                                                   CaptionML=ENU=Filename;
                                                   SourceExpr=txtFileName }
      { 1000000001;Label  ;330  ;110  ;4180 ;440  ;ParentControl=1 }
      { 1000000002;TextBox;4620 ;660  ;1700 ;440  ;InPage=-1;
                                                   CaptionML=ENU=Import table number;
                                                   SourceExpr=nTableID;
                                                   TableRelation=Object.ID;
                                                   OnValidate=VAR
                                                                lrecObject@1000000000 : Record 2000000001;
                                                              BEGIN
                                                                lrecObject.SETRANGE(Type, lrecObject.Type::Table);
                                                                lrecObject.SETRANGE(ID, nTableID);
                                                                lrecObject.FIND('-');
                                                                txtTableName := lrecObject.Name;
                                                              END;

                                                   OnLookup=VAR
                                                              lrecObject@1000000001 : Record 2000000001;
                                                            BEGIN
                                                              lrecObject.SETRANGE(Type, lrecObject.Type::Table);
                                                              IF FORM.RUNMODAL(FORM::Objects, lrecObject) = ACTION::LookupOK THEN
                                                                Text := FORMAT(lrecObject.ID);

                                                              EXIT(TRUE);
                                                            END;
                                                             }
      { 1000000003;Label  ;330  ;660  ;4180 ;440  ;ParentControl=1000000002 }
      { 1000000004;OptionButton;4620;3520;440;440 ;InPage=-1;
                                                   ShowCaption=No;
                                                   CaptionML=ENU=Validate fields starting with "*";
                                                   SourceExpr=bValidateAll;
                                                   OptionValue=No }
      { 1000000005;Label  ;330  ;3520 ;4180 ;440  ;ParentControl=1000000004 }
      { 1000000006;OptionButton;4620;4070;440;440 ;InPage=-1;
                                                   ShowCaption=No;
                                                   CaptionML=ENU=Validate all fields;
                                                   SourceExpr=bValidateAll;
                                                   OptionValue=Yes }
      { 1000000007;Label  ;330  ;4070 ;4180 ;440  ;ParentControl=1000000006 }
      { 1000000000;TextBox;6380 ;660  ;4400 ;440  ;Editable=No;
                                                   InPage=-1;
                                                   SourceExpr=txtTableName }
      { 1000000008;CheckBox;4620;1430 ;440  ;440  ;ShowCaption=No;
                                                   CaptionML=ENU=Allow record inserts;
                                                   SourceExpr=bAllowInserts;
                                                   OnPush=BEGIN
                                                            IF NOT bAllowInserts THEN
                                                             bAllowUpdates := TRUE;
                                                          END;
                                                           }
      { 1000000009;Label  ;330  ;1430 ;4180 ;440  ;ParentControl=1000000008;
                                                   InPage=-1 }
      { 1000000010;CheckBox;4620;1980 ;440  ;440  ;ShowCaption=No;
                                                   CaptionML=ENU=Allow record updates;
                                                   SourceExpr=bAllowUpdates;
                                                   OnPush=BEGIN
                                                            IF NOT bAllowUpdates THEN
                                                              bAllowInserts := TRUE;
                                                          END;
                                                           }
      { 1000000011;Label  ;330  ;1980 ;4180 ;440  ;ParentControl=1000000010;
                                                   InPage=-1 }
      { 1000000012;CheckBox;4620;2750 ;440  ;440  ;ShowCaption=No;
                                                   CaptionML=ENU=Use change log if set up;
                                                   SourceExpr=bDoChangeLog;
                                                   OnPush=BEGIN
                                                            IF NOT bAllowUpdates THEN
                                                              bAllowInserts := TRUE;
                                                          END;
                                                           }
      { 1000000013;Label  ;330  ;2750 ;4180 ;440  ;ParentControl=1000000012;
                                                   InPage=-1 }
    }
  }
  CODE
  {
    VAR
      atxtFieldData@1000000000 : ARRAY [150] OF Text[100];
      bGotHeadings@1000000001 : Boolean;
      abFieldValidate@1000000002 : ARRAY [150] OF Boolean;
      rrRecRef@1000000003 : RecordRef;
      nTableID@1000000004 : Integer;
      anFieldRefIDs@1000000005 : ARRAY [150] OF Integer;
      nFileFldCount@1000000006 : Integer;
      bValidateAll@1000000007 : Boolean;
      txtFileName@1000000008 : Text[250];
      txtTableName@1000000009 : Text[50];
      bAllowUpdates@1000000011 : Boolean;
      bAllowInserts@1000000010 : Boolean;
      bDoChangeLog@1000000012 : Boolean;
      cuChangeLog@1000000013 : Codeunit 423;

    PROCEDURE doAfterImportRecord@1000000006();
    VAR
      lbDidInsert@1000000000 : Boolean;
      lrrOldRec@1000000001 : RecordRef;
    BEGIN
      //<DMS author="M.Hamblin" date="11/12/2010" issue="UDP" >
      // Main loop - maps fields from first row then inserts or updates data
      //</DMS>

      IF NOT bGotHeadings THEN
        getFieldNames
      ELSE BEGIN
        populateFields;

        IF bAllowInserts THEN BEGIN
          IF NOT bAllowUpdates THEN   BEGIN
            rrRecRef.INSERT; // generate error if insert fails and updates not allowed
            lbDidInsert := TRUE;
          END ELSE
            lbDidInsert := rrRecRef.INSERT;

          IF lbDidInsert AND bDoChangeLog THEN
            cuChangeLog.LogInsertion(rrRecRef);
        END;//allow inserts

        IF bAllowUpdates AND NOT lbDidInsert THEN BEGIN
          IF rrRecRef.FIND('=') THEN BEGIN
            IF bDoChangeLog THEN
              lrrOldRec := rrRecRef.DUPLICATE;

            populateFields;
            rrRecRef.MODIFY;

            IF bDoChangeLog THEN
              cuChangeLog.LogModification(rrRecRef, lrrOldRec);
          END;//if
        END;//allow updates
      END;//if
    END;

    LOCAL PROCEDURE getFieldNames@1000000001();
    VAR
      lfrField@1000000000 : FieldRef;
      lnFileFldIndex@1000000001 : Integer;
      lnNavFldCount@1000000002 : Integer;
      lnNavFldIndex@1000000003 : Integer;
      ltxtSQLFieldName@1000000004 : Text[100];
      ltcMisingField@1000000005 : TextConst 'ENU=The file field %1 does not exist in table %2\Continue?';
    BEGIN
      //<DMS author="M.Hamblin" date="11/10/2010" issue="UDP" >
      // maps fields to underlying file
      //</DMS>

      formatFileFields;

      // create field map from file field to record field (anFieldRefIDs)
      lnNavFldCount := rrRecRef.FIELDCOUNT;
      FOR lnFileFldIndex := 1 TO nFileFldCount DO BEGIN

        lnNavFldIndex := 1;
        REPEAT
          lfrField := rrRecRef.FIELDINDEX(lnNavFldIndex);
          ltxtSQLFieldName := UPPERCASE(CONVERTSTR(lfrField.NAME, '."\/''', '_____'));
          IF (atxtFieldData[lnFileFldIndex] = UPPERCASE(lfrField.NAME)) OR
             (atxtFieldData[lnFileFldIndex] = ltxtSQLFieldName)
          THEN
            anFieldRefIDs[lnFileFldIndex] := lnNavFldIndex;

          lnNavFldIndex += 1;
        UNTIL (anFieldRefIDs[lnFileFldIndex]<>0) OR (lnNavFldIndex > lnNavFldCount);

        IF anFieldRefIDs[lnFileFldIndex]=0 THEN
          IF NOT CONFIRM(ltcMisingField, TRUE, atxtFieldData[lnFileFldIndex], txtTableName) THEN
            ERROR('');
      END;//for fld index

      bGotHeadings := TRUE;
    END;

    LOCAL PROCEDURE formatFileFields@1000000000();
    BEGIN
      //<DMS author="M.Hamblin" date="11/12/2010" issue="UDP" >
      // Cleans up file fields and calculates field count
      //</DMS>

      // get count of fields in file (compressarray not safe)
      nFileFldCount := 1;
      WHILE (atxtFieldData[nFileFldCount]<>'') AND (nFileFldCount<150) DO BEGIN
        atxtFieldData[nFileFldCount] := UPPERCASE(atxtFieldData[nFileFldCount]);
        IF COPYSTR(atxtFieldData[nFileFldCount], 1, 1)='*' THEN BEGIN
          abFieldValidate[nFileFldCount] := TRUE;
          atxtFieldData[nFileFldCount] := COPYSTR(atxtFieldData[nFileFldCount], 2, 50);
        END;//if

        abFieldValidate[nFileFldCount] := abFieldValidate[nFileFldCount] OR bValidateAll;
        nFileFldCount += 1;
      END;//while

      nFileFldCount -= 1;
    END;

    LOCAL PROCEDURE populateFields@1000000002();
    VAR
      lnFileFldIndex@1000000000 : Integer;
      lfrField@1000000001 : FieldRef;
      ltxtValue@1000000002 : Text[100];
      ldtDateValue@1000000003 : Date;
      ldDecValue@1000000004 : Decimal;
      ldtDateTimeValue@1000000005 : DateTime;
    BEGIN
      //<DMS author="M.Hamblin" date="11/11/2010" issue="UDP" >
      // Populates the fields in the recordref based on file data
      //</DMS>

      FOR lnFileFldIndex := 1 TO nFileFldCount DO BEGIN
        IF anFieldRefIDs[lnFileFldIndex]<>0 THEN BEGIN

          lfrField := rrRecRef.FIELDINDEX(anFieldRefIDs[lnFileFldIndex]);
          ltxtValue := FORMAT(atxtFieldData[lnFileFldIndex]);

          doInternalPreProcessing(lfrField, ltxtValue);

          CASE FORMAT(lfrField.TYPE) OF
            'Option':
              lfrField.VALUE(getOptionFromText(ltxtValue, lfrField));
            'Code', 'Text':
              lfrField.VALUE(ltxtValue);
            'Date':
            BEGIN
              EVALUATE(ldtDateValue, ltxtValue);
              lfrField.VALUE(ldtDateValue);
            END;
            'DateTime':
            BEGIN
              EVALUATE(ldtDateTimeValue, ltxtValue);
              lfrField.VALUE(ldtDateTimeValue);
            END;
            'Boolean':
              lfrField.VALUE(getBoolFromText(ltxtValue));
            ELSE BEGIN
              EVALUATE(ldDecValue, ltxtValue);
              lfrField.VALUE(ldDecValue);
            END;//else
          END;//case

          IF abFieldValidate[lnFileFldIndex] THEN
            lfrField.VALIDATE;
        END;//if mapped field index not 0
      END;//for
    END;

    LOCAL PROCEDURE getBoolFromText@1000000038(ptxtValue@1000000000 : Text[100]) : Boolean;
    BEGIN
      //<DMS author="M.Hamblin" date="11/12/2010" issue="UDP" >
      // Converts a text string to boolean - string can be 1,0,Y[es],N[o],T[rue],F[alse]
      //</DMS>

      IF UPPERCASE(COPYSTR(ptxtValue,1,1)) IN ['1','Y','T'] THEN
        EXIT(TRUE);

      EXIT(FALSE);
    END;

    LOCAL PROCEDURE getOptionFromText@1000000003(ptxtValue@1000000000 : Text[100];pfrFieldRef@1000000001 : FieldRef) rnOptionVal : Integer;
    VAR
      ltxtOptionStr@1000000002 : Text[500];
      lnStrPos@1000000004 : Integer;
      ltcBadOption@1000000005 : TextConst 'ENU=%1 on line %2 is not a valid option for field %3.\Continue?';
      lnCharPos@1000000006 : Integer;
    BEGIN
      //<DMS author="M.Hamblin" date="11/12/2010" issue="UDP" >
      // Gets an option value (i.e., ordninal value) based on import text
      // todo: possibly cache option strings per field
      //</DMS>

      IF NOT EVALUATE(rnOptionVal, ptxtValue) THEN BEGIN
        ltxtOptionStr := UPPERCASE(pfrFieldRef.OPTIONCAPTION) +',';
        lnStrPos := STRPOS(ltxtOptionStr, UPPERCASE(ptxtValue)+',');
        IF lnStrPos = 0 THEN
          IF NOT CONFIRM(ltcBadOption, TRUE, ptxtValue, diInteger.Number, pfrFieldRef.NAME) THEN
            ERROR('')
          ELSE
            EXIT(0);

        WHILE lnCharPos<lnStrPos DO BEGIN
          lnCharPos += 1;
          IF ltxtOptionStr[lnCharPos]=',' THEN
            rnOptionVal += 1;
        END;//if
      END;//if
    END;

    LOCAL PROCEDURE doInternalPreProcessing@1000000010(pfrField@1000000000 : FieldRef;VAR ptxtValue@1000000001 : Text[100]);
    VAR
      lrecItemUOM@1000000002 : Record 5404;
      lrecResourceUOM@1000000003 : Record 205;
    BEGIN
      //<DMS author="M.Hamblin" date="11/12/2010" issue="UDP" >
      // Does pre-processing of data for specific fields
      // Base functionality is to add item units of measure, resource UOM and contact no. series
      //</DMS>

      IF (nTableID=DATABASE::Item) AND (pfrField.NUMBER=8) THEN BEGIN
        lrecItemUOM."Item No." := rrRecRef.FIELD(1).VALUE;
        lrecItemUOM.Code := ptxtValue;
        lrecItemUOM."Qty. per Unit of Measure" := 1;
        IF lrecItemUOM.INSERT THEN ;
      END//if need to set item UOM
      ELSE IF (nTableID=DATABASE::Resource) AND (pfrField.NUMBER=18) THEN BEGIN
        lrecResourceUOM."Resource No." := rrRecRef.FIELD(1).VALUE;
        lrecResourceUOM.Code := ptxtValue;
        lrecResourceUOM."Qty. per Unit of Measure" := 1;
        IF lrecResourceUOM.INSERT THEN ;
      END//if
      ELSE IF (nTableID=DATABASE::Contact) AND (pfrField.NUMBER=1) AND (ptxtValue='') THEN BEGIN
        ptxtValue := getContactNo; // use no. series to assign contact #
      END;//if
    END;

    PROCEDURE getContactNo@1000000004() rtxtContactNo : Text[100];
    VAR
      lrecMktngSetup@1000000000 : Record 5079;
      lcNoSeriesMgmt@1000000001 : Codeunit 396;
    BEGIN
      //<DMS author="M.Hamblin" date="12/12/2011" issue="--" >
      // Returns a contact # from the assigned number series
      //</DMS>
      lrecMktngSetup.GET;
      EXIT(lcNoSeriesMgmt.GetNextNo(lrecMktngSetup."Contact Nos.", TODAY, TRUE));
    END;

    BEGIN
    {
      ************************
      Copyright Notice

      This objects content is copyright of Dynamic Manufacturing Solutions Inc 2010.  All rights reserved.
      Reproduction. modification, or distribution of part or all of the contents in any form is prohibited
      unless this copyright notice is included.

      Code is free for personal and commercial use, modification, or redistribution.

      www.dynms.com
      ************************

      <DMS>
       <REVISION author="M.Hamblin" date="11/11/2010" version="DMS" issue="UDP">
        Universal data port - will import data for any table
        Requires tab-separated file with first row containing field names.
        Field names prefixed with an asterix ("*") can be validated based on request form options
        Sample Item file format ("<tab>" means a tab character):
          "No." <tab> "*Description" <tab> "Last Modified Date"
          "123" <tab> "My description" <tab> "3/11/2010"
          "456" <tab> "My description 2" <tab> "6/8/08"
       </REVISION>
      </DMS>
    }
    END.
  }
}

